﻿Imports System.Data.SqlClient
Imports vnisc
Public Class frmHRM_Dangkinghi

#Region "Ham va thu tuc"
    Function GetThu(ByVal stt As Integer) As String
        Try
            If stt = 1 Then
                Return "T2"
            ElseIf stt = 2 Then
                Return "T3"
            ElseIf stt = 3 Then
                Return "T4"
            ElseIf stt = 4 Then
                Return "T5"
            ElseIf stt = 5 Then
                Return "T6"
            ElseIf stt = 6 Then
                Return "T7"
            Else
                Return "CN"
            End If
        Catch ex As Exception
            Return ""
        End Try
    End Function

    Function GetDateOfMont(ByVal m As Integer, ByVal y As Integer) As Integer
        Dim d As Date
        Try
            d = CDate("01/" & m.ToString & "/" & y.ToString)
            Return ((d.AddMonths(1)).AddDays(-1)).Day
        Catch ex As Exception
            MsgBox(ex.Message)
            Return 0
        End Try
    End Function

    Function GetFirstOfMont(ByVal m As Integer, ByVal y As Integer) As Date

        Try
            Return CDate("01/" & m.ToString & "/" & y.ToString)

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
    End Function

    Private Sub InitFPS()
        Dim ds As New DataSet
        Dim strSQL As String
        Dim objDAL As New clsDal
        Dim arr() As String
        Try
            strSQL = "Select ReasonID,ReasonName From AbsenceReason Order by ReasonID "
            ds = objDAL.fcnExecQuery(strSQL)
            If ds.Tables(0).Rows.Count > 0 Then
                ReDim arr(ds.Tables(0).Rows.Count - 1)
                For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                    arr(i) = ds.Tables(0).Rows(i).Item("ReasonID").ToString & " - " & ds.Tables(0).Rows(i).Item("ReasonName").ToString
                Next
            End If
            Dim cb As New FarPoint.Win.Spread.CellType.ComboBoxCellType
            cb.Items() = arr
            cb.AutoSearch = FarPoint.Win.AutoSearch.SingleCharacter
            cb.Editable = False
            cb.ListWidth = 200

            For j As Integer = 3 To Me.fps_Sheet1.ColumnCount - 1
                Me.fps_Sheet1.Columns(j).CellType = cb
            Next
            Dim im As FarPoint.Win.Spread.InputMap
            im = fps.GetInputMap(FarPoint.Win.Spread.InputMapMode.WhenAncestorOfFocused)
            im.Put(New FarPoint.Win.Spread.Keystroke(Keys.Enter, Keys.None), FarPoint.Win.Spread.SpreadActions.MoveToNextColumn)
            im = fps.GetInputMap(FarPoint.Win.Spread.InputMapMode.WhenFocused)
            im.Put(New FarPoint.Win.Spread.Keystroke(Keys.Enter, Keys.None), FarPoint.Win.Spread.SpreadActions.MoveToNextColumn)
        Catch ex As Exception
            clsCommon.uError("InitFPS: " & ex.Message)
        Finally
            objDAL = Nothing
            ds = Nothing
        End Try
    End Sub

    Private Sub InitFPS1(ByVal r As Integer, ByVal c As Integer)
        Dim ds As New DataSet
        Dim strSQL As String
        Dim objDAL As New clsDal
        Dim arr() As String
        Try
            strSQL = "Select ReasonID,ReasonName From AbsenceReason Order by ReasonID "
            ds = objDAL.fcnExecQuery(strSQL)
            If ds.Tables(0).Rows.Count > 0 Then
                ReDim arr(ds.Tables(0).Rows.Count - 1)
                For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                    arr(i) = ds.Tables(0).Rows(i).Item("ReasonID").ToString & " - " & ds.Tables(0).Rows(i).Item("ReasonName").ToString
                Next
            End If
            Dim cb As New FarPoint.Win.Spread.CellType.ComboBoxCellType
            cb.Items() = arr
            cb.AutoSearch = FarPoint.Win.AutoSearch.SingleCharacter
            cb.Editable = False
            cb.ListWidth = 200

            Me.fps_Sheet1.Cells(r, c).CellType = cb

            'For j As Integer = 3 To Me.fps_Sheet1.ColumnCount - 1
            '    Me.fps_Sheet1.Columns(j).CellType = cb
            'Next

            Dim im As FarPoint.Win.Spread.InputMap
            im = fps.GetInputMap(FarPoint.Win.Spread.InputMapMode.WhenAncestorOfFocused)
            im.Put(New FarPoint.Win.Spread.Keystroke(Keys.Enter, Keys.None), FarPoint.Win.Spread.SpreadActions.MoveToNextColumn)
            im = fps.GetInputMap(FarPoint.Win.Spread.InputMapMode.WhenFocused)
            im.Put(New FarPoint.Win.Spread.Keystroke(Keys.Enter, Keys.None), FarPoint.Win.Spread.SpreadActions.MoveToNextColumn)
        Catch ex As Exception
            clsCommon.uError("InitFPS: " & ex.Message)
        Finally
            objDAL = Nothing
            ds = Nothing
        End Try
    End Sub

    Sub InitHeaderFPS()
        Dim max As Integer
        Try
            max = GetDateOfMont(Me.dtNgay.Value.Date.Month, Me.dtNgay.Value.Date.Year)
            Me.fps_Sheet1.ColumnHeader.Cells(0, 0).Text = "ID"
            Me.fps_Sheet1.ColumnHeader.Cells(0, 1).Text = "Mã NV"
            Me.fps_Sheet1.ColumnHeader.Cells(0, 2).Text = "Họ tên nhân viên"
            Me.fps_Sheet1.ColumnCount = max + 3
            ' InitFPS()
            Me.fps_Sheet1.Columns(0).Width = 0
            Me.fps_Sheet1.Columns(1).Width = 80
            Me.fps_Sheet1.Columns(2).Width = 160
            Me.fps_Sheet1.Columns(1).VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center
            Me.fps_Sheet1.Columns(1).HorizontalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center

            Me.fps_Sheet1.Columns(1).Locked = True
            Me.fps_Sheet1.Columns(2).Locked = True

            For i As Integer = 1 To max
                Me.fps_Sheet1.ColumnHeader.Cells(0, i).BackColor = Color.Aqua
                Me.fps_Sheet1.Columns(i + 2).HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center
                Me.fps_Sheet1.Columns(i + 2).VerticalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center

            Next

            For i As Integer = 1 To max

                Me.fps_Sheet1.ColumnHeader.Cells(0, i + 2).Text = i.ToString & "-" & GetThu((GetFirstOfMont(Me.dtNgay.Value.Date.Month, Me.dtNgay.Value.Date.Year).AddDays(i - 1)).DayOfWeek)
                If GetThu((GetFirstOfMont(Me.dtNgay.Value.Date.Month, Me.dtNgay.Value.Date.Year).AddDays(i - 1)).DayOfWeek) = "CN" Then
                    Me.fps_Sheet1.ColumnHeader.Cells(0, i + 2).BackColor = Color.Red
                End If
                Me.fps_Sheet1.Columns(i + 2).Width = 40
            Next
        Catch ex As Exception
            clsCommon.uError("InitHeaderFPS: " & ex.Message)
        End Try
    End Sub

    Sub LoadData(ByVal dieukien As String)
       
        Dim max As Integer
        Dim sSQL As String
        Dim ds As New DataSet
        Dim objDal As New clsDal
        Try
            max = GetDateOfMont(Month(Me.dtNgay.Value), Year(Me.dtNgay.Value))

            sSQL = "Select 0, B.EmployeeID, B.EmployeeName, " & vbNewLine
            For i As Integer = 1 To max
                sSQL &= "[" & i.ToString & "],"
            Next
            sSQL &= " GioConBu From RegisterAbsenceReason A,(" & vbNewLine
            sSQL &= " Select E.EmployeeID, E.EmployeeName,DPT.DepartmentID" & vbNewLine
            sSQL &= " From Department DPT, ChangeDepartment CDPT, Employees E" & vbNewLine
            sSQL &= " Where DPT.DepartmentID = CDPT.DepartmentID AND CDPT.EmployeeID = E.EmployeeID " & vbNewLine
            sSQL &= " AND  CDPT.ChangedDate =(SELECT  Top 1 ChangedDate FROM ChangeDepartment "
            sSQL &= " WHERE  Datediff(m, ChangedDate, Convert(DateTime, '28' + '/'+ '" & Me.dtNgay.Value.Month & "' + '/' + '" & Me.dtNgay.Value.Year & "', 103)) >=0 And EmployeeID = E.EmployeeID "
            sSQL &= " Order By ChangedDate Desc) " & vbNewLine
            sSQL &= " AND E.Visible = 1 ) B " & vbNewLine
            sSQL &= " Where A.EmployeeID=B.EmployeeID " & vbNewLine

            If dieukien.Trim <> "" Then
                sSQL &= dieukien
            End If
            ds = objDal.fcnExecQuery(sSQL)

            Me.fps_Sheet1.DataSource = ds.Tables(0)
            ' InitFPS()
        Catch ex As Exception
            clsCommon.uError("LoadData: " & ex.Message)
        Finally
            ds.Dispose()
            ds = Nothing
            objDal = Nothing
        End Try
    End Sub

    Sub Insert_RegisterAbsenceReason()
       
        Dim sSQL As String = ""
        Dim objdal As New clsDal
        Try
            Cursor.Current = Cursors.WaitCursor
            sSQL = "  Insert into RegisterAbsenceReason(EmployeeID, Month,Year) " & vbNewLine
            sSQL &= " Select EmployeeID,'" & Me.dtNgay.Value.Date.Month & "','" & Me.dtNgay.Value.Date.Year & "' " & vbNewLine
            sSQL &= " From Employees "
            sSQL &= " Where EmployeeID Not in (Select EmployeeID  From RegisterAbsenceReason   "
            sSQL &= " Where  Month=" & Me.dtNgay.Value.Date.Month & vbNewLine
            sSQL &= " AND Year=" & Me.dtNgay.Value.Date.Year & vbNewLine
            sSQL &= " )"
            objdal.fcnExecNonQuery(sSQL)
            Cursor.Current = Cursors.Default
        Catch ex As Exception
            clsCommon.uError("Insert_RegisterAbsenceReason: " & ex.Message)
        End Try
    End Sub
#End Region

    Private Sub frmHRM_Dangkinghi_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        OpenConnect()
        Dim str As String
        Dim da As SqlDataAdapter
        Dim cmd As SqlCommand
        Dim Thang, Nam As Integer
        Dim Dept As String
        Thang = Me.dtNgay.Value.Month
        Nam = Me.dtNgay.Value.Year
        Dept = Me.cboPhongban.SelectedValue
        str = "Exec dbo.Update_CongNghi_Luon " & Thang & "," & Nam & ",'" & Dept & "' " ' Thang, Nam, Dept
        cmd = New SqlCommand(str, sqlConn)
        cmd.ExecuteNonQuery()
        CloseConnect()
    End Sub

    Private Sub frmHRM_Dangkinghi_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try

            InitHeaderFPS()
            clsCommon.subLoadComboData(Me.cboPhongban, "DepartmentID", "DepartmentName", "Department")
            'Load_Dept()
            'InitFPS()
            Me.fps_Sheet1.RowCount = 0
        Catch ex As Exception
            clsCommon.uError("Form Error: " & ex.Message)
        End Try
    End Sub
    Sub Load_Dept()
        Dim qr As String = "SELET * FROM Department"
        Dim da As SqlDataAdapter = New SqlDataAdapter(qr, sqlConn)
        Dim dt As New DataTable
        dt.Clear()
        With Me.cboPhongban
            .DataSource = dt
            .DisplayMember = "DepartmentName"
            .ValueMember = "DepartmentID"
        End With
    End Sub
    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim sWhere As String = ""
        Try
            Cursor.Current = Cursors.WaitCursor
            Insert_RegisterAbsenceReason()
            InitHeaderFPS()
            If Me.cboPhongban.SelectedValue <> 0 Then
                sWhere &= " AND B.DepartmentID=" & Me.cboPhongban.SelectedValue & vbNewLine
            End If
            sWhere &= " AND A.Month=" & Month(Me.dtNgay.Value) & vbNewLine
            sWhere &= " AND A.Year=" & Year(Me.dtNgay.Value) & vbNewLine
            If Me.txtMaNV.Text.Trim <> "" Then
                sWhere &= " AND B.EmployeeID like N'" & Me.txtMaNV.Text.Trim & "%'" & vbNewLine
            End If
            If Me.txtTenNV.Text.Trim <> "" Then
                sWhere &= " AND B.EmployeeName like N'" & Me.txtMaNV.Text.Trim & "%'" & vbNewLine
            End If
            sWhere &= " ORDER By  B.EmployeeID  " & vbNewLine
            LoadData(sWhere)
            Cursor.Current = Cursors.Default

        Catch ex As Exception
            clsCommon.uError("btnSearch_Click Error: " & ex.Message)
        End Try

    End Sub

    Private Sub btnThoat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThoat.Click
        OpenConnect()
        Dim str As String
        Dim da As SqlDataAdapter
        Dim cmd As SqlCommand
        Dim Thang, Nam As Integer
        Dim Dept As String
        Thang = Me.dtNgay.Value.Month
        Nam = Me.dtNgay.Value.Year
        Dept = Me.cboPhongban.SelectedValue
        str = "Exec dbo.Update_CongNghi_Luon " & Thang & "," & Nam & ",'" & Dept & "' " ' Thang, Nam, Dept
        cmd = New SqlCommand(str, sqlConn)
        cmd.ExecuteNonQuery()
        CloseConnect()
        Me.Close()
    End Sub

    Private Sub fps_CellClick(ByVal sender As System.Object, ByVal e As FarPoint.Win.Spread.CellClickEventArgs) Handles fps.CellClick

    End Sub

    Private Sub fps_CellDoubleClick(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.CellClickEventArgs) Handles fps.CellDoubleClick
        Dim objDal As New clsDal
        Dim sSQL As String = ""
        Dim m_EmployeeID As String

        Dim frm As New FrmDetail(Me.fps_Sheet1.GetValue(Me.fps_Sheet1.ActiveRowIndex, 1), Me.cboPhongban.SelectedValue)
        Try
            'Me.btnDangKi.PerformClick()
            For i As Integer = 0 To Me.fps_Sheet1.RowCount - 1
                m_EmployeeID = Me.fps_Sheet1.GetText(i, 1).ToString
                sSQL = "UPDATE RegisterAbsenceReason SET " & vbNewLine
                For j As Integer = 1 To GetDateOfMont(Me.dtNgay.Value.Date.Month, Me.dtNgay.Value.Date.Year)
                    sSQL &= "  [" & (j).ToString & "]='" & Me.fps_Sheet1.GetText(i, j + 2).ToString & "'," & vbNewLine

                Next
                sSQL = Mid(sSQL, 1, sSQL.Trim.Length - 1)
                sSQL &= " Where  Month=" & Me.dtNgay.Value.Date.Month & vbNewLine
                sSQL &= " AND Year=" & Me.dtNgay.Value.Date.Year & vbNewLine
                sSQL &= " AND EmployeeID=N'" & m_EmployeeID & "'"
                objDal.fcnExecNonQuery(sSQL)
            Next

            If Me.fps_Sheet1.ActiveColumnIndex = 1 Or Me.fps_Sheet1.ActiveColumnIndex = 2 Then
                frm.EmployeeID = Me.fps_Sheet1.GetValue(Me.fps_Sheet1.ActiveRowIndex, 1)
                frm.EmployeeName = Me.fps_Sheet1.GetValue(Me.fps_Sheet1.ActiveRowIndex, 2)
                frm.DepartmenrName = Me.cboPhongban.Text
                frm.DepartmentID = Me.cboPhongban.SelectedValue
                frm.ShowInTaskbar = False
                frm.StartPosition = FormStartPosition.CenterScreen
                AddHandler frm.save, AddressOf Reload
                frm.ShowDialog()
                'Me.btnSearch.PerformClick()
            End If
        Catch ex As Exception
            clsCommon.uError("fps_CellDoubleClick Error: " & ex.Message)
        End Try
    End Sub

    Sub Reload()
        Me.btnSearch.PerformClick()
    End Sub

    Private Sub fps_EditModeOff(ByVal sender As Object, ByVal e As System.EventArgs) Handles fps.EditModeOff
        'InitFPS1(Me.fps_Sheet1.ActiveRowIndex, Me.fps_Sheet1.ActiveColumnIndex)
    End Sub

    Private Sub fps_EditModeOn(ByVal sender As Object, ByVal e As System.EventArgs) Handles fps.EditModeOn

    End Sub

    Private Sub fps_EditModeStarting(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.EditModeStartingEventArgs) Handles fps.EditModeStarting
        ' InitFPS1(Me.fps_Sheet1.ActiveRowIndex, Me.fps_Sheet1.ActiveColumnIndex)
    End Sub

    Private Sub fps_EnterCell(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.EnterCellEventArgs) Handles fps.EnterCell
        If e.Column > 2 Then
            InitFPS1(Me.fps_Sheet1.ActiveRowIndex, Me.fps_Sheet1.ActiveColumnIndex)
        End If
    End Sub

    Private Sub fps_LeaveCell(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.LeaveCellEventArgs) Handles fps.LeaveCell
        Dim t As String
        t = ""
        Try
            If e.Column > 2 Then
                t = Me.fps_Sheet1.GetText(e.Row, e.Column).ToString.Trim


                Dim cb As New FarPoint.Win.Spread.CellType.GeneralCellType

                Me.fps_Sheet1.Cells(e.Row, e.Column).CellType = cb
                If t.Length > 0 Then
                    Me.fps_Sheet1.SetValue(e.Row, e.Column, Mid(t, 1, InStr(t, " -") - 1))
                End If
            End If

            If t.Length > 0 Then
                Me.fps_Sheet1.SetValue(e.Row, e.Column, Mid(t, 1, InStr(t, " -") - 1))
                Me.fps_Sheet1.Cells(e.Row, e.Column).BackColor = Color.Orange
            End If

        Catch ex As Exception
            clsCommon.uError("fps_LeaveCell Error: " & ex.Message)
        End Try
    End Sub

    Private Sub btnDangKi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDangKi.Click
        Dim objDal As New clsDal
        Dim sSQL As String = ""
        Dim m_EmployeeID As String
        Try
            Cursor.Current = Cursors.WaitCursor

            If Me.fps_Sheet1.RowCount = 0 Then Exit Sub

            For i As Integer = 0 To Me.fps_Sheet1.RowCount - 1
                m_EmployeeID = Me.fps_Sheet1.GetText(i, 1).ToString
                sSQL = "UPDATE RegisterAbsenceReason SET " & vbNewLine
                For j As Integer = 1 To GetDateOfMont(Me.dtNgay.Value.Date.Month, Me.dtNgay.Value.Date.Year)
                    sSQL &= "  [" & (j).ToString & "]='" & Me.fps_Sheet1.GetText(i, j + 2).ToString & "'," & vbNewLine

                Next
                sSQL = Mid(sSQL, 1, sSQL.Trim.Length - 1)
                sSQL &= " Where  Month=" & Me.dtNgay.Value.Date.Month & vbNewLine
                sSQL &= " AND Year=" & Me.dtNgay.Value.Date.Year & vbNewLine
                sSQL &= " AND EmployeeID=N'" & m_EmployeeID & "'"
                objDal.fcnExecNonQuery(sSQL)

                For j As Integer = 1 To GetDateOfMont(Me.dtNgay.Value.Date.Month, Me.dtNgay.Value.Date.Year)
                    sSQL &= "  [" & (j).ToString & "]='" & Me.fps_Sheet1.GetText(i, j + 2).ToString & "'," & vbNewLine
                    Me.fps_Sheet1.Cells(i, j).BackColor = Color.White
                    Me.fps_Sheet1.SetActiveCell(i, j)
                Next

            Next

           

            clsCommon.uMsgboxInfo("Đăng kí nghỉ thành công!")
            'Cursor.Current = Cursors.Default
        Catch ex As Exception
            clsCommon.uError("fps_LeaveCell Error: " & ex.Message)
        End Try
    End Sub
End Class
